<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Country extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "country";
		$this->prefix = "cou";
	}

	function getCountry($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "couName", $dir = "ASC") {
		$fieldsQuery = Common::fieldsquery($fields, $query);
		$fieldsQuery = str_replace("cou_city_name", "city_name", $fieldsQuery);
		if(is_array($order)) {
			$str = "";
			foreach($order as $order_tag) {
				$str .= (($str)?(", "):("")).Common::underscore($order_tag);
			}
			$order = $str;
		} else {
			$order = Common::coalesce(array($order, "couName"));
			$order = (($order == "rand()")?($order):(Common::underscore($order)));
		}
		$dir = Common::coalesce(array($dir, "ASC"));

		$qs =	"
					select
						cou_id, cou_name, cou_url_name, cou_con_id, con_name, con_url_name, cou_priority, cou_latitude, cou_longitude, cou_presenter, cou_data, if(cou_data = 'N' and cou_presenter = 'I', '".$this->db->escape_str($this->lang->line("YES"))."', '') as cou_data2, t.e2i_image_name, t.e2i_alt, t2.e2c_temp_flag
						, count(distinct maj_id) as majCnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin() and !defined("OVERRIDE"))?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_cou_id = cou_id) "):(""))."

						left join ".$this->db->dbprefix."city on cou_id = city_cou_id and city_del = 0
						left join ".$this->db->dbprefix."institute on city_id = ins_city_id and ins_del = 0
						left join ".$this->db->dbprefix."major on ins_id = maj_ins_id and maj_del = 0 ".
						//left join ".$this->db->dbprefix."ent2cat on e2c_entity = 'COU' and cou_id = e2c_ent_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null
						"left join (
							select
								e2i_cou_id, e2i_image_name, e2i_alt
							from
								".$this->db->dbprefix."ent2image
							where
								e2i_cou_id is not null and e2i_del = 0
							order by
								rand()
						) as t on cou_id = t.e2i_cou_id
						left join (
							select
								e2c_ent_id, e2c_temp_flag
							from
								".$this->db->dbprefix."ent2cat 
							where
								e2c_entity = 'COU' and e2c_del = 0 and e2c_temp_flag = 'I'
							group by
								e2c_ent_id
						) as t2 on t2.e2c_ent_id = cou_id
					where
						cou_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					group by
						cou_id
					order by
						".(($order == "rand()")?($order):($order." ".$dir))."
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
					";
		$result = $this->db->query($qs);
		$getCountry = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {

				$thumb = preg_split("/\./", $row->e2i_image_name);
				$len = strlen($thumb[count($thumb)-1]);

				$getCountry[] = array(
					"couId" => $row->cou_id,
					"couName" => $row->cou_name,
					"couUrlName" => $row->cou_url_name,
					"couConId" => $row->cou_con_id,
					"conName" => $row->con_name,
					"conUrlName" => $row->con_url_name,
					"couPriority" => $row->cou_priority,
					"couImageName" => $row->e2i_image_name,
					"couImageNameThumb" => (($row->e2i_image_name)?(substr($row->e2i_image_name, 0, -($len+1))."_thumb.".$thumb[count($thumb)-1]):("")),
					"couLatitude" => $row->cou_latitude,
					"couLongitude" => $row->cou_longitude,
					"couImageAlt" => (($row->e2i_alt)?($row->e2i_alt):("")),
					"e2cTempFlag" => $row->e2c_temp_flag,
					"majCnt" => $row->majCnt,
					"couPresenter" => $row->cou_presenter,
					"couData" => $row->cou_data,
					"couData2" => $row->cou_data2,
				);
			}

		}

		return $getCountry;
	}

	function getCountryCount($where = NULL, $fields, $query) {

		$fieldsQuery = Common::fieldsquery($fields, $query);

		$qs =	"
					select
						count(*) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((is_array($this->session->userdata("grpName")) and Common::isAdmin())?
						(" join ".$this->db->dbprefix."usr2ent on u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_cou_id = cou_id) "):(""))."

					where
						cou_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

	function getCityInstitute($where = NULL) {
		$qs =	"
					select
						con_url_name, cou_url_name, city_id, city_name, city_url_name, city_latitude, city_longitude, ins_id, ins_ins_id, ins_name, ins_url_name
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						join ".$this->db->dbprefix."city on cou_id = city_cou_id and city_del = 0
						left join ".$this->db->dbprefix."institute on city_id = ins_city_id and ins_del = 0
						left join ".$this->db->dbprefix."major on ins_id = maj_ins_id and maj_del = 0
					where
						cou_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					group by
						ins_id
					order by
						city_name, ins_name
					";
					$this->db->cache_off();
		$result = $this->db->query($qs);
		//Common::pr($this->db->last_query());
		$getCityInstitute = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getCityInstitute[$row->city_id]["cityName"] = $row->city_name;
				$getCityInstitute[$row->city_id]["cityUrlName"] = $row->city_url_name;
				$getCityInstitute[$row->city_id]["cityLatitude"] = $row->city_latitude;
				$getCityInstitute[$row->city_id]["cityLongitude"] = $row->city_longitude;
				$getCityInstitute[$row->city_id]["couUrlName"] = $row->cou_url_name;
				$getCityInstitute[$row->city_id]["conUrlName"] = $row->con_url_name;
				if($row->ins_id) {
					$getCityInstitute[$row->city_id]["ins"][$row->ins_id]["insId"] = $row->ins_id;
					$getCityInstitute[$row->city_id]["ins"][$row->ins_id]["insInsId"] = $row->ins_ins_id;
					$getCityInstitute[$row->city_id]["ins"][$row->ins_id]["insName"] = $row->ins_name;
					$getCityInstitute[$row->city_id]["ins"][$row->ins_id]["insUrlName"] = $row->ins_url_name;
				}
			}
		}
		return $getCityInstitute;
	}

}